﻿using Newtonsoft.Json.Linq;
using NLog;
using Persistence.Core;
using QueryDataLogic.Core;
using System;
using System.Data.SqlClient;
using System.IO;
using System.Linq;

namespace PersistenceDataLogic.Core
{
    public class OperationBasic
    {
        private static Logger logger = LogManager.GetCurrentClassLogger();
        PersistenceData pd = new PersistenceData();
        QueryData qd = new QueryData();
        UserVerify uv = new UserVerify();
        BasicInfo bi = new BasicInfo();
        string sqlFilePath = "";

        public OperationBasic()
        {
            sqlFilePath = Directory.GetCurrentDirectory() + @"\MealSystem.sql";
        }

        /// <summary>
        /// 判断食堂编码是否存在
        /// </summary>
        /// <returns></returns>
        private bool IsDiningHallNumber(string number, string dbName)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select hallid AS id,hallNumber AS number,hallName as name,districtID from dininghall where hallNumber = @number"
                };
                comm.Parameters.AddWithValue("@number", number);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 判断食堂编码是否被报餐信息引用
        /// </summary>
        /// <returns></returns>
        private bool IsDiningHallCiteMeal(int HallID, string dbName)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select ID from ReportMeals where HallId = @HallId"
                };
                comm.Parameters.AddWithValue("@HallId", HallID);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 根据食堂编码获取ID
        /// </summary>
        /// <returns></returns>
        private int GetHallID(int districtID, string number, string dbName)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select hallID from DiningHall where hallNumber = @number and districtID = @districtID "
                };
                comm.Parameters.AddWithValue("@number", number);
                comm.Parameters.AddWithValue("@districtID", districtID);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    return (int)ja[0]["hallID"];
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
            }
            return 0;
        }

        /// <summary>
        /// 新增、修改、删除食堂
        /// </summary>
        /// <returns></returns>
        public JObject OptDiningHall(JObject joParam)
        {
            JObject joResult = new JObject();

            try
            {
                int id = 0;
                //新增时 ID 可以不传
                if (joParam.Property("id") != null)
                {
                    id = (int)joParam["id"];
                }
                string state = (string)joParam["state"];
                string name = (string)joParam["name"];
                string number = (string)joParam["number"];
                int districtID = (int)joParam["districtID"];
                //截止时间 允许为空 兼容线上版本
                string deadline = "";
                if (joParam.Property("deadline") != null)
                {
                    deadline = (string)joParam["deadline"];
                }
                int isEnable = 1;
                if (joParam.Property("isEnable") != null)
                {
                    isEnable = (bool)joParam["isEnable"] == true ? 1 : 0;
                }
                string dbName = bi.GetDBName(districtID);
                ////有传递 位置参数 则写入 没有传递 则写入空字串
                //if (joParam.Property("districtID") != null)
                //{
                //    districtID = (int)joParam["districtID"];
                //}

                int resultLine = 0;
                SqlCommand comm = new SqlCommand();
                if (state == "add")
                {
                    if (!IsDiningHallNumber(number, dbName))
                    {
                        comm.CommandText = "INSERT INTO DiningHall(HallNumber,HallName,DistrictID,deadline,isEnable) VALUES(@HallNumber,@HallName,@DistrictID,@deadline,@isEnable) ";

                        comm.Parameters.AddWithValue("@HallName", name);
                        comm.Parameters.AddWithValue("@HallNumber", number);
                        comm.Parameters.AddWithValue("@DistrictID", districtID);
                        comm.Parameters.AddWithValue("@deadline", deadline);
                        comm.Parameters.AddWithValue("@isEnable", isEnable);
                        resultLine = pd.ExeSqlCode(comm, dbName);

                        if (resultLine > 0)
                        {
                            id = GetHallID(districtID, number, dbName);
                            //初始化属性
                            SqlCommand commAttribute = new SqlCommand
                            {
                                CommandText = "INSERT INTO Attribute(Type,Number,Name,Remark,HallID,DistrictID) VALUES(0,'Monday','周一','',@id,@districtID) " +
                                "INSERT INTO Attribute(Type,Number,Name,Remark,HallID,DistrictID) VALUES(0,'Tuesday','周二','',@id,@districtID) " +
                                "INSERT INTO Attribute(Type,Number,Name,Remark,HallID,DistrictID) VALUES(0,'Wednesday','周三','',@id,@districtID) " +
                                "INSERT INTO Attribute(Type,Number,Name,Remark,HallID,DistrictID) VALUES(0,'Thursday','周四','',@id,@districtID) " +
                                "INSERT INTO Attribute(Type,Number,Name,Remark,HallID,DistrictID) VALUES(0,'Friday','周五','',@id,@districtID) " +
                                "INSERT INTO Attribute(Type,Number,Name,Remark,HallID,DistrictID) VALUES(0,'Saturday','周六','',@id,@districtID) " +
                                "INSERT INTO Attribute(Type,Number,Name,Remark,HallID,DistrictID) VALUES(0,'Sunday','周日','',@id,@districtID) "
                            };
                            commAttribute.Parameters.AddWithValue("@id", id);
                            commAttribute.Parameters.AddWithValue("@DistrictID", districtID);
                            resultLine = pd.ExeSqlCode(commAttribute, dbName);

                            joResult["message"] = "添加食堂" + number + "成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "添加食堂" + number + "失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["data"] = 0;
                        joResult["message"] = "食堂编码已经存在，请重新添加";
                        joResult["state"] = false;
                        return joResult;
                    }
                }
                else if (state == "modify")
                {
                    //if (!IsDiningHallNumber(number, dbName))
                    //{
                    comm.CommandText = "UPDATE DiningHall SET HallName = @HallName,DistrictID = @DistrictID,deadline=@deadline,isEnable=@isEnable WHERE HallId = @HallID ";

                    comm.Parameters.AddWithValue("@HallID", id);
                    comm.Parameters.AddWithValue("@HallName", name);
                    comm.Parameters.AddWithValue("@HallNumber", number);
                    comm.Parameters.AddWithValue("@DistrictID", districtID);
                    comm.Parameters.AddWithValue("@deadline", deadline);
                    comm.Parameters.AddWithValue("@isEnable", isEnable);

                    resultLine = pd.ExeSqlCode(comm, dbName);

                    if (resultLine > 0)
                    {
                        joResult["message"] = "修改食堂" + number + "成功";
                        joResult["data"] = resultLine;
                        joResult["state"] = true;
                    }
                    else
                    {
                        joResult["message"] = "修改食堂" + number + "失败";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                    //}
                    //else
                    //{
                    //    joResult["data"] = 0;
                    //    joResult["message"] = "食堂编码已经存在，修改失败";
                    //    joResult["state"] = false;
                    //    return joResult;
                    //}
                }
                else if (state == "del")
                {
                    if (!IsDiningHallCiteMeal(id, dbName))
                    {
                        comm.CommandText = "DELETE FROM Attribute WHERE HallId = @HallID " +
                            "DELETE FROM DiningHall WHERE HallId = @HallID ";

                        comm.Parameters.AddWithValue("@HallID", id);

                        resultLine = pd.ExeSqlCode(comm, dbName);

                        if (resultLine > 0)
                        {
                            joResult["message"] = "删除食堂" + number + "成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "删除食堂" + number + "失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["message"] = "食堂“" + name + "”被报餐信息引用，无法删除";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult["data"] = 0;
                joResult["message"] = ex.Message + "\n" + ex.StackTrace;
                joResult["state"] = false;
            }

            return joResult;
        }

        /// <summary>
        /// 判断餐点编码是否存在
        /// </summary>
        /// <returns></returns>
        private bool IsMealNumber(string number, string dbName)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select mealid AS id,mealNumber AS number,mealName as name,price from mealTime where mealNumber = @number"
                };
                comm.Parameters.AddWithValue("@number", number);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 判断餐点编码是否被报餐信息引用
        /// </summary>
        /// <returns></returns>
        private bool IsMealTimeCiteMeal(int MealID, string dbName)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select ID from ReportMeals where MealID = @MealID"
                };
                comm.Parameters.AddWithValue("@MealID", MealID);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 新增、修改、删除餐点费用
        /// </summary>
        /// <returns></returns>
        public JObject OptMealTime(JObject joParam)
        {
            JObject joResult = new JObject();

            try
            {
                int id = 0;
                //新增时 ID 可以不传
                if (joParam.Property("id") != null)
                {
                    id = (int)joParam["id"];
                }
                string state = (string)joParam["state"];
                string name = (string)joParam["name"];
                string number = (string)joParam["number"];
                decimal price = (decimal)joParam["price"];
                decimal FamilyPrice = (decimal)joParam["familyPrice"];
                int DistrictID = (int)joParam["districtID"];
                string startTime;
                string endTime;
                if (joParam.Property("startTime") != null)
                {
                    startTime = (string)joParam["startTime"];
                }
                else
                {
                    startTime = null;
                }
                if (joParam.Property("endTime") != null)
                {
                    endTime = (string)joParam["endTime"];
                }
                else
                {
                    endTime = null;
                }
                string dbName = bi.GetDBName(DistrictID);

                int resultLine = 0;
                SqlCommand comm = new SqlCommand();
                if (state == "add")
                {
                    if (!IsMealNumber(number, dbName))
                    {
                        if (startTime == null || endTime == null)
                        {
                            comm.CommandText = "INSERT INTO MealTime(MealNumber,MealName,Price,FamilyPrice,DistrictID) VALUES(@MealNumber,@MealName,@Price,@FamilyPrice,@DistrictID)";
                        }
                        else
                        {
                            comm.CommandText = "INSERT INTO MealTime(MealNumber,MealName,Price,FamilyPrice,DistrictID,startTime,endTime) VALUES(@MealNumber,@MealName,@Price,@FamilyPrice,@DistrictID,@startTime,@endTime)";
                        }
                        comm.Parameters.AddWithValue("@MealName", name);
                        comm.Parameters.AddWithValue("@MealNumber", number);
                        comm.Parameters.AddWithValue("@Price", price);
                        comm.Parameters.AddWithValue("@FamilyPrice", FamilyPrice);
                        comm.Parameters.AddWithValue("@DistrictID", DistrictID);
                        comm.Parameters.AddWithValue("@startTime", startTime);
                        comm.Parameters.AddWithValue("@endTime", endTime);

                        resultLine = pd.ExeSqlCode(comm, dbName);

                        if (resultLine > 0)
                        {
                            joResult["message"] = "添加餐点" + number + "成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "添加餐点" + number + "失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["data"] = 0;
                        joResult["message"] = "餐点编码已经存在，请重新添加";
                        joResult["state"] = false;
                        return joResult;
                    }
                }
                else if (state == "modify")
                {
                    //if (!IsMealNumber(number, dbName))
                    //{
                    if (startTime == null || endTime == null)
                    {
                        comm.CommandText = "UPDATE MealTime SET MealName = @MealName,Price = @Price,FamilyPrice = @FamilyPrice WHERE MealId = @MealId ";
                    }
                    else
                    {
                        comm.CommandText = "UPDATE MealTime SET MealName = @MealName,Price = @Price,FamilyPrice = @FamilyPrice,startTime = @startTime,endTime = @endTime WHERE MealId = @MealId ";
                    }
                    comm.Parameters.AddWithValue("@MealId", id);
                    comm.Parameters.AddWithValue("@MealName", name);
                    comm.Parameters.AddWithValue("@MealNumber", number);
                    comm.Parameters.AddWithValue("@Price", price);
                    comm.Parameters.AddWithValue("@FamilyPrice", FamilyPrice);
                    comm.Parameters.AddWithValue("@startTime", startTime);
                    comm.Parameters.AddWithValue("@endTime", endTime);

                    resultLine = pd.ExeSqlCode(comm, dbName);

                    if (resultLine > 0)
                    {
                        joResult["message"] = "修改餐点" + number + "成功";
                        joResult["data"] = resultLine;
                        joResult["state"] = true;
                    }
                    else
                    {
                        joResult["message"] = "修改餐点" + number + "失败";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                    //}
                    //else
                    //{
                    //    joResult["data"] = 0;
                    //    joResult["message"] = "餐点编码已经存在，修改失败";
                    //    joResult["state"] = false;
                    //    return joResult;
                    //}
                }
                else if (state == "del")
                {
                    if (!IsMealTimeCiteMeal(id, dbName))
                    {
                        comm.CommandText = "DELETE FROM MealTime WHERE MealId = @MealId ";

                        comm.Parameters.AddWithValue("@MealId", id);

                        resultLine = pd.ExeSqlCode(comm, dbName);

                        if (resultLine > 0)
                        {
                            joResult["message"] = "删除餐点" + number + "成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "删除餐点" + number + "失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["message"] = "餐点“" + name + "”被报餐信息引用，无法删除";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                }

            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult["data"] = 0;
                joResult["message"] = ex.Message + "\n" + ex.StackTrace;
                joResult["state"] = false;
            }

            return joResult;
        }

        /// <summary>
        /// 判断手机号码是否被用户注册
        /// </summary>
        /// <returns></returns>
        private bool IsMobileCiteMealUser(string mobile, string dbName)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select UserID from MealUser where mobile = @mobile"
                };
                comm.Parameters.AddWithValue("@mobile", mobile);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 新增、修改、删除通讯录
        /// </summary>
        /// <returns></returns>
        public JObject OptAddressBook(JObject joParam)
        {
            JObject joResult = new JObject();

            try
            {
                int id = 0;
                //新增时 ID 可以不传
                if (joParam.Property("id") != null)
                {
                    id = (int)joParam["id"];
                }
                string state = (string)joParam["state"];
                string number = null;
                if (joParam.Property("number") != null)
                {
                    number = (string)joParam["number"];
                }
                string name = (string)joParam["name"];
                string mobile = (string)joParam["mobile"];
                int districtID = (int)joParam["districtID"];
                string dbName = bi.GetDBName(districtID);
                int resultLine = 0;
                SqlCommand comm = new SqlCommand();
                if (state == "add")
                {
                    if (!bi.IsMobile(mobile, dbName))
                    {
                        comm.CommandText = "INSERT INTO AddressBook(number,name,mobile,DistrictID) VALUES(@number,@name,@mobile,@DistrictID)";

                        comm.Parameters.AddWithValue("@number", number);
                        comm.Parameters.AddWithValue("@name", name);
                        comm.Parameters.AddWithValue("@mobile", mobile);
                        comm.Parameters.AddWithValue("@DistrictID", districtID);

                        resultLine = pd.ExeSqlCode(comm, dbName);

                        if (resultLine > 0)
                        {
                            joResult["message"] = "添加通讯录" + mobile + "成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "添加通讯录" + mobile + "失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["data"] = 0;
                        joResult["message"] = "手机号码已经存在，请重新添加";
                        joResult["state"] = false;
                        return joResult;
                    }
                }
                else if (state == "modify")
                {
                    //if (!IsMobile(mobile, dbName))
                    //{
                    comm.CommandText = "UPDATE AddressBook SET number=@number,name = @name,mobile = @mobile,DistrictID = @DistrictID WHERE id = @id ";

                    comm.Parameters.AddWithValue("@id", id);
                    comm.Parameters.AddWithValue("@number", number);
                    comm.Parameters.AddWithValue("@name", name);
                    comm.Parameters.AddWithValue("@mobile", mobile);
                    comm.Parameters.AddWithValue("@DistrictID", districtID);

                    resultLine = pd.ExeSqlCode(comm, dbName);
                    if (resultLine > 0)
                    {
                        joResult["message"] = "修改通讯录" + mobile + "成功";
                        joResult["data"] = resultLine;
                        joResult["state"] = true;
                    }
                    else
                    {
                        joResult["message"] = "修改通讯录" + mobile + "失败";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                    //}
                    //else
                    //{
                    //    joResult["data"] = 0;
                    //    joResult["message"] = "手机号码已经存在，修改失败";
                    //    joResult["state"] = false;
                    //    return joResult;
                    //}
                }
                else if (state == "del")
                {
                    if (!IsMobileCiteMealUser(mobile, dbName))
                    {
                        comm.CommandText = "DELETE FROM AddressBook WHERE id = @id ";

                        comm.Parameters.AddWithValue("@id", id);

                        resultLine = pd.ExeSqlCode(comm, dbName);

                        if (resultLine > 0)
                        {
                            joResult["message"] = "删除通讯录" + mobile + "成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "删除通讯录" + mobile + "失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["message"] = "用户“" + name + "”注册了该手机号码，无法删除通讯录";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult["data"] = 0;
                joResult["message"] = ex.Message + "\n" + ex.StackTrace;
                joResult["state"] = false;
            }

            return joResult;
        }

        /// <summary>
        /// 判断评价编码是否存在
        /// </summary>
        /// <param name="number"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        public bool IsEvaluation(string number, string dbName)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select id from EvaluationContent WHERE number = @number "
                };

                comm.Parameters.AddWithValue("@number", number);

                JArray ja = qd.GetData(comm, dbName);
                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 判断评价项目是否被用引用
        /// </summary>
        /// <returns></returns>
        private bool IsEvaluationCiteMealUser(int ContentID, string dbName)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select ectr.id from EvaluationContentToResult ectr " +
                    "inner join EvaluationContent ec on ectr.ContentID = ec.ID " +
                    "inner join EvaluationUser eu on eu.ECTRID = ectr.ID " +
                    "where ectr.ContentID = @ContentID"
                };
                comm.Parameters.AddWithValue("@ContentID", ContentID);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 获取评价项目ID
        /// </summary>
        /// <param name="districtID"></param>
        /// <returns></returns>
        private int GetEvaluationContentID(string number, int districtID)
        {
            int id = 0;
            try
            {
                string dbName = "";

                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select id from EvaluationContent " +
                    "where districtID = @districtID and number = @number order by id "
                };
                comm.Parameters.AddWithValue("@districtID", districtID);
                comm.Parameters.AddWithValue("@number", number);

                dbName = bi.GetDBName(districtID);
                JArray ja = qd.GetData(comm, dbName);
                if (ja.Count > 0)
                {
                    id = (int)ja[0]["id"];
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
            }
            return id;
        }

        /// <summary>
        /// 获取评价结果
        /// </summary>
        /// <param name="districtID"></param>
        /// <returns></returns>
        private JArray GetEvaluationResult(int districtID)
        {
            JArray ja = new JArray();
            try
            {
                string dbName = "";

                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select id,name,number,districtID from EvaluationResult " +
                    "where districtID = @districtID order by id "
                };
                comm.Parameters.AddWithValue("@districtID", districtID);

                dbName = bi.GetDBName(districtID);

                ja = qd.GetData(comm, dbName);
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
            }
            return ja;
        }

        /// <summary>
        /// 新增、修改、删除评价项目
        /// </summary>
        /// <returns></returns>
        public JObject OptEvaluation(JObject joParam)
        {
            JObject joResult = new JObject();

            try
            {
                int id = 0;
                //新增时 ID 可以不传
                if (joParam.Property("id") != null)
                {
                    id = (int)joParam["id"];
                }
                string state = (string)joParam["state"];
                string name = (string)joParam["name"];
                string number = (string)joParam["number"];
                string remark = "";
                if (joParam.Property("remark") != null)
                {
                    remark = (string)joParam["remark"];
                }
                int districtID = (int)joParam["districtID"];
                int isEnable = 1;
                if (joParam.Property("isEnable") != null)
                {
                    isEnable = (bool)joParam["isEnable"] == true ? 1 : 0;
                }
                string dbName = bi.GetDBName(districtID);
                int resultLine = 0;
                SqlCommand comm = new SqlCommand();

                if (state == "add")
                {
                    if (!IsEvaluation(number, dbName))
                    {
                        comm.CommandText = "INSERT INTO EvaluationContent(name,number,DistrictID,remark,isEnable) VALUES(@name,@number,@DistrictID,@remark,@isEnable)";

                        comm.Parameters.AddWithValue("@name", name);
                        comm.Parameters.AddWithValue("@number", number);
                        comm.Parameters.AddWithValue("@remark", remark);
                        comm.Parameters.AddWithValue("@DistrictID", districtID);
                        comm.Parameters.AddWithValue("@isEnable", isEnable);

                        resultLine = pd.ExeSqlCode(comm, dbName);

                        if (resultLine > 0)
                        {
                            //绑定评价结果
                            SqlCommand commEvaluation;
                            JArray jaEvaluationResult = GetEvaluationResult(districtID);
                            JObject joEvaluationResult = new JObject();
                            int ResultID = 0;
                            int ContentID = GetEvaluationContentID(number, districtID);
                            int EvaluationResultLine = 0;
                            for (int resultIndex = 0; resultIndex < jaEvaluationResult.Count; resultIndex++)
                            {
                                commEvaluation = new SqlCommand();
                                joEvaluationResult = (JObject)jaEvaluationResult[resultIndex];
                                ResultID = (int)joEvaluationResult["id"];

                                commEvaluation.CommandText = "INSERT INTO EvaluationContentToResult(ContentID,ResultID,DistrictID) VALUES(@ContentID,@ResultID,@DistrictID)";

                                commEvaluation.Parameters.AddWithValue("@ContentID", ContentID);
                                commEvaluation.Parameters.AddWithValue("@ResultID", ResultID);
                                commEvaluation.Parameters.AddWithValue("@DistrictID", districtID);

                                EvaluationResultLine += pd.ExeSqlCode(commEvaluation, dbName);
                            }

                            if (EvaluationResultLine > 0)
                            {
                                joResult["message"] = "添加评价项目 " + number + " 并绑定评价结果成功";
                                joResult["data"] = resultLine;
                                joResult["state"] = true;
                            }
                            else
                            {
                                SqlCommand commDeleteEvaluation = new SqlCommand();
                                //删除评价项目
                                commDeleteEvaluation.CommandText = "DELETE FROM EvaluationContentToResult WHERE ContentID = @id; " +
                                           "DELETE FROM EvaluationContent WHERE id = @id ";

                                commDeleteEvaluation.Parameters.AddWithValue("@id", id);

                                resultLine = pd.ExeSqlCode(commDeleteEvaluation, dbName);

                                joResult["message"] = "添加评价项目 " + number + " 失败";
                                joResult["data"] = resultLine;
                                joResult["state"] = false;
                            }
                        }
                        else
                        {
                            joResult["message"] = "添加评价项目 " + number + " 失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["data"] = 0;
                        joResult["message"] = "评价项目已经存在，请重新添加";
                        joResult["state"] = false;
                        return joResult;
                    }
                }
                else if (state == "modify")
                {
                    //if (!IsEvaluationCiteMealUser(id, dbName))
                    //{
                    if (!IsEvaluation(number, dbName))
                    {
                        comm.CommandText = "UPDATE EvaluationContent SET name = @name,remark=@remark,isEnable=@isEnable WHERE id = @id ";

                        comm.Parameters.AddWithValue("@id", id);
                        comm.Parameters.AddWithValue("@name", name);
                        comm.Parameters.AddWithValue("@number", number);
                        comm.Parameters.AddWithValue("@remark", remark);
                        comm.Parameters.AddWithValue("@DistrictID", districtID);
                        comm.Parameters.AddWithValue("@isEnable", isEnable);

                        resultLine = pd.ExeSqlCode(comm, dbName);
                        if (resultLine > 0)
                        {
                            joResult["message"] = "修改评价项目 " + number + " 成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "修改评价项目 " + number + " 失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                        //}
                        //else
                        //{
                        //    joResult["data"] = 0;
                        //    joResult["message"] = "评价项目已经存在，修改失败";
                        //    joResult["state"] = false;
                        //    return joResult;
                        //}
                    }
                    else
                    {
                        joResult["message"] = "评价项目“" + number + "”已被使用，无法修改";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                }
                else if (state == "del")
                {
                    if (!IsEvaluationCiteMealUser(id, dbName))
                    {
                        comm.CommandText = "DELETE FROM EvaluationContentToResult WHERE ContentID = @id; " +
                                           "DELETE FROM EvaluationContent WHERE id = @id ";

                        comm.Parameters.AddWithValue("@id", id);

                        resultLine = pd.ExeSqlCode(comm, dbName);

                        if (resultLine > 0)
                        {
                            joResult["message"] = "删除评价项目 " + number + " 成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "删除评价项目 " + number + " 失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["message"] = "评价项目“" + number + "”已被使用，无法删除，请禁用";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult["data"] = 0;
                joResult["message"] = ex.Message + "\n" + ex.StackTrace;
                joResult["state"] = false;
            }

            return joResult;
        }

        /// <summary>
        /// 禁用评价项目
        /// </summary>
        /// <returns></returns>
        public JObject DisableEvaluation(JObject joParam)
        {
            JObject joResult = new JObject();
            try
            {
                int id = (int)joParam["id"];
                bool isEnable = (bool)joParam["isEnable"];
                int districtID = (int)joParam["districtID"];
                string number = (string)joParam["number"];
                string dbName = bi.GetDBName(districtID);
                int resultLine = 0;
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "UPDATE EvaluationContent SET isEnable=@isEnable WHERE id = @id and districtID = @districtID ";

                comm.Parameters.AddWithValue("@id", id);
                comm.Parameters.AddWithValue("@DistrictID", districtID);
                comm.Parameters.AddWithValue("@isEnable", isEnable);

                resultLine = pd.ExeSqlCode(comm, dbName);
                if (resultLine > 0)
                {
                    joResult["message"] = "禁用/启用评价项目 " + number + " 成功";
                    joResult["data"] = resultLine;
                    joResult["state"] = true;
                }
                else
                {
                    joResult["message"] = "禁用/启用评价项目 " + number + " 失败";
                    joResult["data"] = resultLine;
                    joResult["state"] = false;
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult["data"] = 0;
                joResult["message"] = ex.Message + "\n" + ex.StackTrace;
                joResult["state"] = false;
            }
            return joResult;
        }

        /// <summary>
        /// 判断区域编码是否存在
        /// </summary>
        /// <returns></returns>
        private bool IsDistrictNumber(string number)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select DistrictID AS id,DistrictNumber AS number,DistrictName as name from District where DistrictNumber = @number"
                };
                comm.Parameters.AddWithValue("@number", number);
                JArray ja = qd.GetData(comm, "MealManage");

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 判断区域编码是否被使用
        /// </summary>
        /// <returns></returns>
        private bool IsDistrictCiteAddressBook(int DistrictID)
        {
            try
            {
                string dbName = bi.GetDBName(DistrictID);

                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select ID from AddressBook where DistrictID = @DistrictID"
                };
                comm.Parameters.AddWithValue("@DistrictID", DistrictID);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 获取区域ID
        /// </summary>
        /// <returns></returns>
        private int GetDistrictID(string number)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select DistrictID AS id,DistrictNumber AS number,DistrictName as name from District where DistrictNumber = @number"
                };
                comm.Parameters.AddWithValue("@number", number);
                JArray ja = qd.GetData(comm, "MealManage");
                logger.Info(ja);
                if (ja.Count > 0)
                {
                    return (int)ja[0]["id"];
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return 0;
            }
        }

        /// <summary>
        /// 初始化数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        private bool InitData(int id, string dbName)
        {
            try
            {
                int resultLine = 0;
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "IF NOT EXISTS(SELECT * FROM AddressBook WHERE mobile = '18050568012' and DistrictID = @districtID) " +
                                   "BEGIN " +
                                   "INSERT INTO AddressBook(number,name, mobile, DistrictID) VALUES('admin','管理员','18050568012',@districtID) " +
                                   "END " +
                                   "IF NOT EXISTS(SELECT * FROM AddressBook WHERE mobile = '13959562997' and DistrictID = @districtID) " +
                                   "BEGIN " +
                                   "INSERT INTO AddressBook(number,name, mobile, DistrictID) VALUES('642997', '郑剑文','13959562997',@districtID) " +
                                   "END " +
                                   "IF NOT EXISTS(SELECT * FROM MealUser WHERE mobile = '18050568012' and DistrictID = @districtID) " +
                                   "BEGIN " +
                                   "INSERT INTO MealUser(mobile, Number, Name, DistrictID,isAdmin,isManager,isTemporary, AddressBookID) VALUES('18050568012','admin','管理员',@districtID,1,1,1,1) " +
                                   "END " +
                                   "IF NOT EXISTS(SELECT * FROM MealUser WHERE mobile = '13959562997' and DistrictID = @districtID) " +
                                   "BEGIN " +
                                   "INSERT INTO MealUser(mobile, Number, Name, DistrictID,isAdmin,isManager,isTemporary, AddressBookID) VALUES('13959562997','642997','郑剑文',@districtID,1,1,1,2) " +
                                   "END " +
                                   "IF NOT EXISTS (SELECT * FROM EvaluationResult WHERE number = '01' and DistrictID = @districtID) " +
                                   "BEGIN " +
                                   "insert into EvaluationResult(Number, Name, Remark, DistrictID) values('01', '非常满意', '', @districtID) " +
                                   "END " +
                                   "IF NOT EXISTS(SELECT * FROM EvaluationResult WHERE number = '02' and DistrictID = @districtID) " +
                                   "BEGIN " +
                                   "insert into EvaluationResult(Number, Name, Remark, DistrictID) values('02', '满意', '', @districtID) " +
                                   "END " +
                                   "IF NOT EXISTS(SELECT * FROM EvaluationResult WHERE number = '03' and DistrictID = @districtID) " +
                                   "BEGIN " +
                                   "insert into EvaluationResult(Number, Name, Remark, DistrictID) values('03', '一般', '', @districtID) " +
                                   "END " +
                                   "IF NOT EXISTS(SELECT * FROM EvaluationResult WHERE number = '04' and DistrictID = @districtID) " +
                                   "BEGIN " +
                                   "insert into EvaluationResult(Number, Name, Remark, DistrictID) values('04', '不满意', '', @districtID) " +
                                   "END " +
                                   "insert into MealManage.dbo.SystemSetting(Name, Value, DistrictID) values('ConstraintDishes', 0, @districtID)";
                comm.Parameters.AddWithValue("@districtID", id);
                resultLine = pd.ExeSqlCode(comm, dbName);
                if (resultLine > 0)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
            }
            return false;
        }

        /// <summary>
        /// 新增、修改、删除区域（全部区域）
        /// </summary>
        /// <returns></returns>
        public JObject OptDistrict(JObject joParam)
        {
            JObject joResult = new JObject();

            try
            {
                int id = 0;
                //新增时 ID 可以不传
                if (joParam.Property("id") != null)
                {
                    id = (int)joParam["id"];
                }
                string state = (string)joParam["state"];
                string name = (string)joParam["name"];
                string number = (string)joParam["number"];
                int companyID = (int)joParam["companyID"];
                string dbName = "";
                int resultLine = 0;
                SqlCommand comm = new SqlCommand();
                if (state == "add")
                {
                    if (!IsDistrictNumber(number))
                    {
                        comm.CommandText = "INSERT INTO District(DistrictNumber,DistrictName,CompanyID) VALUES(@DistrictNumber,@DistrictName,@CompanyID)";

                        comm.Parameters.AddWithValue("@DistrictName", name);
                        comm.Parameters.AddWithValue("@DistrictNumber", number);
                        comm.Parameters.AddWithValue("@CompanyID", companyID);

                        resultLine = pd.ExeSqlCode(comm, "MealManage");

                        if (resultLine > 0)
                        {
                            joResult["message"] = "添加区域" + number + "成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;

                            //同步添加该区域的邀请码
                            id = GetDistrictID(number);//获取新增区域的ID
                            dbName = bi.GetDBNameToCompany(companyID);//获取数据库名称

                            int value = uv.GenerateCode();

                            SqlCommand commCode = new SqlCommand()
                            {
                                CommandText = "INSERT INTO InvitationCode(code,districtID,date) VALUES(@code,@districtID,getdate()) "
                            };

                            commCode.Parameters.AddWithValue("@code", value);
                            commCode.Parameters.AddWithValue("@districtID", id);

                            int ResultLine = pd.ExeSqlCode(commCode, "MealManage");

                            //初始化数据
                            InitData(id, dbName);
                        }
                        else
                        {
                            joResult["message"] = "添加区域" + number + "失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["data"] = 0;
                        joResult["message"] = "区域编码已经存在，请重新添加";
                        joResult["state"] = false;
                        return joResult;
                    }
                }
                else if (state == "modify")
                {
                    //if (!IsDistrictNumber(number))
                    //{
                    comm.CommandText = "UPDATE District SET DistrictName = @DistrictName WHERE DistrictId = @DistrictID ";

                    comm.Parameters.AddWithValue("@DistrictID", id);
                    comm.Parameters.AddWithValue("@DistrictName", name);
                    comm.Parameters.AddWithValue("@DistrictNumber", number);

                    resultLine = pd.ExeSqlCode(comm, "MealManage");

                    if (resultLine > 0)
                    {
                        joResult["message"] = "修改区域" + number + "成功";
                        joResult["data"] = resultLine;
                        joResult["state"] = true;
                    }
                    else
                    {
                        joResult["message"] = "修改区域" + number + "失败";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                    //}
                    //else
                    //{
                    //    joResult["data"] = 0;
                    //    joResult["message"] = "区域编码已经存在，修改失败";
                    //    joResult["state"] = false;
                    //    return joResult;
                    //}
                }
                else if (state == "del")
                {
                    if (!IsDistrictCiteAddressBook(id))
                    {
                        comm.CommandText = "DELETE FROM InvitationCode WHERE districtID = @DistrictID; " +
                                           "DELETE FROM District WHERE DistrictId = @DistrictID ";//删除区域同步删除邀请码

                        comm.Parameters.AddWithValue("@DistrictID", id);

                        resultLine = pd.ExeSqlCode(comm, "MealManage");

                        if (resultLine > 0)
                        {
                            joResult["message"] = "删除区域" + number + "成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "删除区域" + number + "失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["message"] = "区域“" + name + "”在系统中被使用，无法删除";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult["data"] = 0;
                joResult["message"] = ex.Message + "\n" + ex.StackTrace;
                joResult["state"] = false;
            }

            return joResult;
        }

        /// <summary>
        /// 新增、修改、删除区域 当前公司
        /// </summary>
        /// <returns></returns>
        public JObject OptDistrictCurrent(JObject joParam)
        {
            JObject joResult = new JObject();

            try
            {
                int id = 0;
                int companyID = 0;
                //新增时 ID 可以不传
                if (joParam.Property("id") != null)
                {
                    id = (int)joParam["id"];
                    companyID = bi.GetCompanyID(id);
                }
                string state = (string)joParam["state"];
                string name = (string)joParam["name"];
                string number = (string)joParam["number"];
                string dbName = "";
                int resultLine = 0;
                SqlCommand comm = new SqlCommand();
                if (state == "add")
                {
                    if (!IsDistrictNumber(number))
                    {
                        comm.CommandText = "INSERT INTO District(DistrictNumber,DistrictName,CompanyID) VALUES(@DistrictNumber,@DistrictName,@CompanyID)";

                        comm.Parameters.AddWithValue("@DistrictName", name);
                        comm.Parameters.AddWithValue("@DistrictNumber", number);
                        comm.Parameters.AddWithValue("@CompanyID", companyID);

                        resultLine = pd.ExeSqlCode(comm, "MealManage");

                        if (resultLine > 0)
                        {
                            joResult["message"] = "添加区域" + number + "成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;

                            //同步添加该区域的邀请码
                            id = GetDistrictID(number);//获取新增区域的ID
                            dbName = bi.GetDBNameToCompany(companyID);//获取数据库名称

                            int value = uv.GenerateCode();

                            SqlCommand commCode = new SqlCommand()
                            {
                                CommandText = "INSERT INTO InvitationCode(code,districtID,date) VALUES(@code,@districtID,getdate()) "
                            };

                            commCode.Parameters.AddWithValue("@code", value);
                            commCode.Parameters.AddWithValue("@districtID", id);

                            int ResultLine = pd.ExeSqlCode(commCode, "MealManage");

                            //初始化数据
                            InitData(id, dbName);
                        }
                        else
                        {
                            joResult["message"] = "添加区域" + number + "失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["data"] = 0;
                        joResult["message"] = "区域编码已经存在，请重新添加";
                        joResult["state"] = false;
                        return joResult;
                    }
                }
                else if (state == "modify")
                {
                    //if (!IsDistrictNumber(number))
                    //{
                    comm.CommandText = "UPDATE District SET DistrictName = @DistrictName WHERE DistrictId = @DistrictID ";

                    comm.Parameters.AddWithValue("@DistrictID", id);
                    comm.Parameters.AddWithValue("@DistrictName", name);
                    comm.Parameters.AddWithValue("@DistrictNumber", number);

                    resultLine = pd.ExeSqlCode(comm, "MealManage");

                    if (resultLine > 0)
                    {
                        joResult["message"] = "修改区域" + number + "成功";
                        joResult["data"] = resultLine;
                        joResult["state"] = true;
                    }
                    else
                    {
                        joResult["message"] = "修改区域" + number + "失败";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                    //}
                    //else
                    //{
                    //    joResult["data"] = 0;
                    //    joResult["message"] = "区域编码已经存在，修改失败";
                    //    joResult["state"] = false;
                    //    return joResult;
                    //}
                }
                else if (state == "del")
                {
                    if (!IsDistrictCiteAddressBook(id))
                    {
                        comm.CommandText = "DELETE FROM InvitationCode WHERE districtID = @DistrictID; " +
                                           "DELETE FROM District WHERE DistrictId = @DistrictID ";//删除区域同步删除邀请码

                        comm.Parameters.AddWithValue("@DistrictID", id);

                        resultLine = pd.ExeSqlCode(comm, "MealManage");

                        if (resultLine > 0)
                        {
                            joResult["message"] = "删除区域" + number + "成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "删除区域" + number + "失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["message"] = "区域“" + name + "”在系统中被使用，无法删除";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult["data"] = 0;
                joResult["message"] = ex.Message + "\n" + ex.StackTrace;
                joResult["state"] = false;
            }

            return joResult;
        }

        /// <summary>
        /// 判断公司编码是否存在
        /// </summary>
        /// <returns></returns>
        private bool IsCompanyNumber(string number)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select id,number,name from Company where Number = @number"
                };
                comm.Parameters.AddWithValue("@number", number);
                JArray ja = qd.GetData(comm, "MealManage");

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 判断公司编码是否被使用
        /// </summary>
        /// <returns></returns>
        private bool IsCompanyCiteMealUser(int CompanyID)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select DistrictID from District where CompanyID = @CompanyID"
                };
                comm.Parameters.AddWithValue("@CompanyID", CompanyID);
                JArray ja = qd.GetData(comm, "MealManage");

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 读取数据库表初始化脚本文件（打开文件并读取文件的所有内容，读取完毕关闭文件）
        /// </summary>
        /// <returns></returns>
        private string ReadCreaterDatabaseSQLScript()
        {
            if (!File.Exists(sqlFilePath))
            {
                FileStream fs = new FileStream(sqlFilePath, FileMode.Create, FileAccess.ReadWrite);
                StreamWriter sw = new StreamWriter(fs);
                sw.Write("");
                sw.Flush();
                sw.Close();
            }
            return File.ReadAllText(sqlFilePath);
        }

        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="databaseName"></param>
        public bool CreaterDatabase(string databaseName)
        {
            try
            {
                int resultLine = 0;
                //string sqlCode = ReadCreaterDatabaseSQLScript();
                string sqlCode = "IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'" + databaseName + "') " +
                                "BEGIN " +
                                "CREATE DATABASE [" + databaseName + "] ON PRIMARY " +
                                "(NAME = N'" + databaseName + "', FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\DATA\\" + databaseName + ".mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) " +
                                " LOG ON " +
                                "(NAME = N'" + databaseName + "_log', FILENAME = N'C:\\Program Files\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\MSSQL\\DATA\\" + databaseName + "_l.ldf' , SIZE = 13632KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) " +
                                "END";
                logger.Info(sqlCode);

                resultLine = pd.ExeSqlCodeWithGo(sqlCode, "master");

                if (resultLine > 0)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
            }
            return false;
        }

        /// <summary>
        /// 初始化数据库表
        /// </summary>
        /// <param name="databaseName"></param>
        public bool InitDatabaseTable(string databaseName)
        {
            try
            {
                int resultLine = 0;
                //string sqlCode = ReadCreaterDatabaseSQLScript();
                string sqlCode = ReadCreaterDatabaseSQLScript();
                logger.Info(sqlCode);

                resultLine += pd.ExeSqlCodeWithGo(sqlCode, databaseName);

                if (resultLine > 0)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
            }
            return false;
        }

        /// <summary>
        /// 新增、修改、删除公司信息
        /// </summary>
        /// <returns></returns>
        public JObject OptCompany(JObject joParam)
        {
            JObject joResult = new JObject();

            try
            {
                int id = 0;
                //新增时 ID 可以不传
                if (joParam.Property("id") != null)
                {
                    id = (int)joParam["id"];
                }
                string state = (string)joParam["state"];
                string name = (string)joParam["name"];
                string number = (string)joParam["number"];
                int? versionID = null;
                if (joParam.Property("versionID") != null)
                {
                    versionID = (int?)joParam["versionID"];
                }
                //一下信息可不传参
                string Address = "";
                if (joParam.Property("address") != null)
                {
                    Address = (string)joParam["address"];
                }
                string Mobile = "";
                if (joParam.Property("mobile") != null)
                {
                    Mobile = (string)joParam["mobile"];
                }
                string Tel = "";
                if (joParam.Property("tel") != null)
                {
                    Tel = (string)joParam["tel"];
                }
                string legal_representative = "";
                if (joParam.Property("legal_representative") != null)
                {
                    legal_representative = (string)joParam["legal_representative"];
                }
                string reg_num = "";
                if (joParam.Property("reg_num") != null)
                {
                    reg_num = (string)joParam["reg_num"];
                }
                string Remark = "";
                if (joParam.Property("remark") != null)
                {
                    Remark = (string)joParam["remark"];
                }
                bool isAudit = false;
                if (joParam.Property("isAudit") != null)
                {
                    isAudit = (bool)joParam["isAudit"];
                }
                object ExpirationDate = null;
                if (joParam.Property("expirationDate") != null)
                {
                    ExpirationDate = (DateTime)joParam["expirationDate"];
                }

                string DatabaseName = "MealSystem" + DateTime.Now.ToString("yyyyMMddHHmmssfff");

                int resultLine = 0;
                bool isDB = false;
                SqlCommand comm = new SqlCommand();
                if (state == "add")
                {
                    if (!IsCompanyNumber(number))
                    {
                        comm.CommandText = "INSERT INTO Company(Number,Name,Address,Mobile,Tel,legal_representative,reg_num,Remark,DatabaseName,isAudit,VersionID, ExpirationDate) VALUES(@Number,@Name,@Address,@Mobile,@Tel,@legal_representative,@reg_num,@Remark,@DatabaseName,@isAudit,@versionID,@ExpirationDate)";

                        comm.Parameters.AddWithValue("@Number", number);
                        comm.Parameters.AddWithValue("@Name", name);
                        comm.Parameters.AddWithValue("@Address", Address);
                        comm.Parameters.AddWithValue("@Mobile", Mobile);
                        comm.Parameters.AddWithValue("@Tel", Tel);
                        comm.Parameters.AddWithValue("@legal_representative", legal_representative);
                        comm.Parameters.AddWithValue("@reg_num", reg_num);
                        comm.Parameters.AddWithValue("@Remark", Remark);
                        comm.Parameters.AddWithValue("@DatabaseName", DatabaseName);
                        comm.Parameters.AddWithValue("@isAudit", isAudit);
                        comm.Parameters.AddWithValue("@versionID", versionID);
                        comm.Parameters.AddWithValue("@ExpirationDate", ExpirationDate == null ? DBNull.Value : ExpirationDate);

                        resultLine = pd.ExeSqlCode(comm, "MealManage");

                        if (resultLine > 0)
                        {
                            //创建该公司的数据库
                            isDB = CreaterDatabase(DatabaseName);
                            isDB = InitDatabaseTable(DatabaseName);
                            //if (isDB)
                            //{
                            joResult["message"] = "添加公司 " + number + " 并创建数据库成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                            //}
                            //else
                            //{
                            //    joResult["message"] = "添加公司 " + number + " 成功,创建数据库失败，请联系管理员处理";
                            //    joResult["data"] = resultLine;
                            //    joResult["state"] = false;
                            //}
                        }
                        else
                        {
                            joResult["message"] = "添加公司 " + number + " 失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["data"] = 0;
                        joResult["message"] = "公司编码已经存在，请重新添加";
                        joResult["state"] = false;
                        return joResult;
                    }
                }
                else if (state == "modify")
                {
                    //if (!IsCompanyNumber(number))
                    //{
                    comm.CommandText = "UPDATE Company SET Name = @Name,Address=@Address,Mobile=@Mobile,Tel=@Tel,legal_representative=@legal_representative,Remark=@Remark,VersionID=@versionID,ExpirationDate=@ExpirationDate WHERE ID = @CompanyID ";

                    comm.Parameters.AddWithValue("@CompanyID", id);
                    comm.Parameters.AddWithValue("@Number", number);
                    comm.Parameters.AddWithValue("@Name", name);
                    comm.Parameters.AddWithValue("@Address", Address);
                    comm.Parameters.AddWithValue("@Mobile", Mobile);
                    comm.Parameters.AddWithValue("@Tel", Tel);
                    comm.Parameters.AddWithValue("@legal_representative", legal_representative);
                    comm.Parameters.AddWithValue("@Remark", Remark);
                    comm.Parameters.AddWithValue("@versionID", versionID);
                    comm.Parameters.AddWithValue("@ExpirationDate", ExpirationDate == null ? DBNull.Value : ExpirationDate);

                    resultLine = pd.ExeSqlCode(comm, "MealManage");

                    if (resultLine > 0)
                    {
                        joResult["message"] = "修改公司 " + number + " 成功";
                        joResult["data"] = resultLine;
                        joResult["state"] = true;
                    }
                    else
                    {
                        joResult["message"] = "修改公司 " + number + " 失败";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                    //}
                    //else
                    //{
                    //    joResult["data"] = 0;
                    //    joResult["message"] = "公司编码已经存在，修改失败";
                    //    joResult["state"] = false;
                    //    return joResult;
                    //}
                }
                else if (state == "del")
                {
                    if (!IsCompanyCiteMealUser(id))
                    {
                        string dbName = bi.GetDBNameToCompany(id);

                        comm.CommandText = "DELETE FROM Company WHERE id = @CompanyID;";

                        comm.Parameters.AddWithValue("@CompanyID", id);
                        comm.Parameters.AddWithValue("@DBName", dbName);

                        resultLine = pd.ExeSqlCode(comm, "MealManage");

                        if (resultLine > 0)
                        {
                            joResult["message"] = "删除公司 " + number + " 成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "删除公司 " + number + " 失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["message"] = "公司“" + name + "”在系统中被区域信息使用，无法删除";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult["data"] = 0;
                joResult["message"] = ex.Message + "\n" + ex.StackTrace;
                joResult["state"] = false;
            }

            return joResult;
        }

        /// <summary>
        /// 判断菜品分类编码是否存在
        /// </summary>
        /// <returns></returns>
        private bool IsDishesClassNumber(string number, int districtID, int hallID, string dbName)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select id from DishesClass where number = @number and districtID = @districtID and hallID = @hallID "
                };
                comm.Parameters.AddWithValue("@number", number);
                comm.Parameters.AddWithValue("@districtID", districtID);
                comm.Parameters.AddWithValue("@hallID", hallID);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 判断菜品分类编码是否被菜品信息引用
        /// </summary>
        /// <returns></returns>
        private bool IsDishesClassCiteDishes(int id, string dbName)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select ID from Dishes where DishesClassID = @id"
                };
                comm.Parameters.AddWithValue("@id", id);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 新增、修改、删除菜品分类
        /// </summary>
        /// <returns></returns>
        public JObject OptDishesClass(JObject joParam)
        {
            JObject joResult = new JObject();

            try
            {
                int id = 0;
                //新增时 ID 可以不传
                if (joParam.Property("id") != null)
                {
                    id = (int)joParam["id"];
                }
                string state = (string)joParam["state"];
                string name = (string)joParam["name"];
                string number = (string)joParam["number"];
                int districtID = (int)joParam["districtID"];
                int hallID = (int)joParam["hallID"];
                int qty = (int)joParam["qty"];
                string remark = "";
                if (joParam.Property("remark") != null)
                {
                    remark = (string)joParam["remark"];
                }
                string dbName = bi.GetDBName(districtID);

                int resultLine = 0;
                SqlCommand comm = new SqlCommand();
                if (state == "add")
                {
                    if (!IsDishesClassNumber(number, districtID, hallID, dbName))
                    {
                        comm.CommandText = "INSERT INTO DishesClass(Number,Name,DistrictID,hallID,remark,qty) VALUES(@Number,@Name,@DistrictID,@hallID,@remark,@qty)";

                        comm.Parameters.AddWithValue("@Number", number);
                        comm.Parameters.AddWithValue("@Name", name);
                        comm.Parameters.AddWithValue("@DistrictID", districtID);
                        comm.Parameters.AddWithValue("@hallID", hallID);
                        comm.Parameters.AddWithValue("@remark", remark);
                        comm.Parameters.AddWithValue("@qty", qty);

                        resultLine = pd.ExeSqlCode(comm, dbName);

                        if (resultLine > 0)
                        {
                            joResult["message"] = "添加菜品分类 " + number + " 成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "添加菜品分类 " + number + " 失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["data"] = 0;
                        joResult["message"] = "菜品分类编码已经存在，请重新添加";
                        joResult["state"] = false;
                        return joResult;
                    }
                }
                else if (state == "modify")
                {
                    comm.CommandText = "UPDATE DishesClass SET Name = @Name,Number = @number,DistrictID = @DistrictID,hallID=@hallID,remark=@remark,qty=@qty WHERE id = @id ";

                    comm.Parameters.AddWithValue("@id", id);
                    comm.Parameters.AddWithValue("@Name", name);
                    comm.Parameters.AddWithValue("@number", number);
                    comm.Parameters.AddWithValue("@DistrictID", districtID);
                    comm.Parameters.AddWithValue("@hallID", hallID);
                    comm.Parameters.AddWithValue("@remark", remark);
                    comm.Parameters.AddWithValue("@qty", qty);

                    resultLine = pd.ExeSqlCode(comm, dbName);

                    if (resultLine > 0)
                    {
                        joResult["message"] = "修改菜品分类 " + number + " 成功";
                        joResult["data"] = resultLine;
                        joResult["state"] = true;
                    }
                    else
                    {
                        joResult["message"] = "修改菜品分类 " + number + " 失败";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                }
                else if (state == "del")
                {
                    if (!IsDishesClassCiteDishes(id, dbName))
                    {
                        comm.CommandText = "DELETE FROM DishesClass WHERE id = @id ";

                        comm.Parameters.AddWithValue("@id", id);

                        resultLine = pd.ExeSqlCode(comm, dbName);

                        if (resultLine > 0)
                        {
                            joResult["message"] = "删除菜品分类 " + number + " 成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "删除菜品分类 " + number + " 失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["message"] = "菜品分类“" + name + "”被菜品信息引用，无法删除";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult["data"] = 0;
                joResult["message"] = ex.Message + "\n" + ex.StackTrace;
                joResult["state"] = false;
            }

            return joResult;
        }

        /// <summary>
        /// 判断菜品属性是否存在
        /// </summary>
        /// <returns></returns>
        private bool IsDishesAttribute(int DishesID, int AttributeID, int districtID, int hallID, string dbName)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select id from DishesAttribute where DishesID = @DishesID and AttributeID = @AttributeID and districtID = @districtID and hallID = @hallID "
                };
                comm.Parameters.AddWithValue("@DishesID", DishesID);
                comm.Parameters.AddWithValue("@AttributeID", AttributeID);
                comm.Parameters.AddWithValue("@districtID", districtID);
                comm.Parameters.AddWithValue("@hallID", hallID);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 新增、修改、删除菜品属性
        /// </summary>
        /// <returns></returns>
        private JObject OptDishesAttribute(JObject joParam)
        {
            JObject joResult = new JObject();

            try
            {
                int id = 0;
                //新增时 ID 可以不传
                if (joParam.Property("id") != null)
                {
                    id = (int)joParam["id"];
                }
                string state = (string)joParam["state"];
                string name = "";
                if (joParam.Property("name") != null)
                {
                    name = (string)joParam["name"];
                }
                int DishesID = (int)joParam["dishesID"];
                int AttributeID = (int)joParam["attributeID"];
                int districtID = (int)joParam["districtID"];
                int hallID = (int)joParam["hallID"];
                string remark = "";
                if (joParam.Property("remark") != null)
                {
                    remark = (string)joParam["remark"];
                }
                string dbName = bi.GetDBName(districtID);

                int resultLine = 0;
                SqlCommand comm = new SqlCommand();
                if (state == "add")
                {
                    if (!IsDishesAttribute(DishesID, AttributeID, districtID, hallID, dbName))
                    {
                        comm.CommandText = "INSERT INTO DishesAttribute(Name,DishesID,AttributeID,DistrictID,hallID,remark) VALUES(@Name,@DishesID,@AttributeID,@DistrictID,@hallID,@remark)";

                        comm.Parameters.AddWithValue("@Name", name);
                        comm.Parameters.AddWithValue("@DishesID", DishesID);
                        comm.Parameters.AddWithValue("@AttributeID", AttributeID);
                        comm.Parameters.AddWithValue("@DistrictID", districtID);
                        comm.Parameters.AddWithValue("@hallID", hallID);
                        comm.Parameters.AddWithValue("@remark", remark);

                        resultLine = pd.ExeSqlCode(comm, dbName);

                        if (resultLine > 0)
                        {
                            joResult["message"] = "添加菜品属性成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "添加菜品属性失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["data"] = 0;
                        joResult["message"] = "菜品属性已经存在，请重新添加";
                        joResult["state"] = false;
                        return joResult;
                    }
                }
                else if (state == "modify")
                {
                    comm.CommandText = "UPDATE DishesAttribute SET Name = @Name,DishesID = @DishesID,AttributeID = @AttributeID,DistrictID = @DistrictID,hallID=@hallID,remark=@remark WHERE id = @id ";

                    comm.Parameters.AddWithValue("@id", id);
                    comm.Parameters.AddWithValue("@Name", name);
                    comm.Parameters.AddWithValue("@DishesID", DishesID);
                    comm.Parameters.AddWithValue("@AttributeID", AttributeID);
                    comm.Parameters.AddWithValue("@DistrictID", districtID);
                    comm.Parameters.AddWithValue("@hallID", hallID);
                    comm.Parameters.AddWithValue("@remark", remark);

                    resultLine = pd.ExeSqlCode(comm, dbName);

                    if (resultLine > 0)
                    {
                        joResult["message"] = "修改菜品属性成功";
                        joResult["data"] = resultLine;
                        joResult["state"] = true;
                    }
                    else
                    {
                        joResult["message"] = "修改菜品属性失败";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                }
                else if (state == "del")
                {
                    //if (!IsDishesClassCiteDishes(id, dbName))
                    //{
                    comm.CommandText = "DELETE FROM DishesAttribute WHERE id = @id ";

                    comm.Parameters.AddWithValue("@id", id);

                    resultLine = pd.ExeSqlCode(comm, dbName);

                    if (resultLine > 0)
                    {
                        joResult["message"] = "删除菜品属性成功";
                        joResult["data"] = resultLine;
                        joResult["state"] = true;
                    }
                    else
                    {
                        joResult["message"] = "删除菜品属性失败";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                    //}
                    //else
                    //{
                    //    joResult["message"] = "菜品已被使用，无法删除";
                    //    joResult["data"] = resultLine;
                    //    joResult["state"] = false;
                    //}
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult["data"] = 0;
                joResult["message"] = ex.Message + "\n" + ex.StackTrace;
                joResult["state"] = false;
            }

            return joResult;
        }

        /// <summary>
        /// 判断菜品编码是否存在
        /// </summary>
        /// <returns></returns>
        private bool IsDishesNumber(string number, int districtID, int hallID, int dishesClassID, string dbName)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select id from Dishes where number = @number and districtID = @districtID and hallID = @hallID and dishesClassID = @dishesClassID"
                };
                comm.Parameters.AddWithValue("@number", number);
                comm.Parameters.AddWithValue("@districtID", districtID);
                comm.Parameters.AddWithValue("@hallID", hallID);
                comm.Parameters.AddWithValue("@dishesClassID", dishesClassID);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 判断菜品分类编码是否被使用
        /// </summary>
        /// <returns></returns>
        private bool IsDishesCiteUserDishes(int id, string dbName)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select ID from UserDishes where DishesID = @id"
                };
                comm.Parameters.AddWithValue("@id", id);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 根据菜品信息获取菜品ID
        /// </summary>
        /// <returns></returns>
        private int GetDishesID(string number, int districtID, int hallID, int dishesClassID, string dbName)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select id from Dishes where number = @number and districtID = @districtID and hallID = @hallID and dishesClassID = @dishesClassID"
                };
                comm.Parameters.AddWithValue("@number", number);
                comm.Parameters.AddWithValue("@districtID", districtID);
                comm.Parameters.AddWithValue("@hallID", hallID);
                comm.Parameters.AddWithValue("@dishesClassID", dishesClassID);
                JArray ja = qd.GetData(comm, dbName);
                JObject jo = (JObject)ja[0];
                if (ja.Count > 0)
                {
                    return (int)jo["id"];
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return 0;
            }
        }

        /// <summary>
        /// 根据菜品ID删除菜品信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="dbName"></param>
        /// <returns></returns>
        private bool DelDishes(int id, string dbName)
        {
            JObject joResult = new JObject();
            try
            {
                int resultLine = 0;
                SqlCommand comm = new SqlCommand
                {
                    CommandText = "DELETE FROM DishesAttribute WHERE DishesID = @id;" +
                    "DELETE FROM Dishes WHERE id = @id; "
                };
                comm.Parameters.AddWithValue("@id", id);
                resultLine = pd.ExeSqlCode(comm, dbName);

                if (resultLine > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 新增、修改、删除菜品
        /// </summary>
        /// <returns></returns>
        public JObject OptDishes(JObject joParam)
        {
            JObject joResult = new JObject();

            try
            {
                int id = 0;
                //新增时 ID 可以不传
                if (joParam.Property("id") != null)
                {
                    id = (int)joParam["id"];
                }
                string state = (string)joParam["state"];
                string name = (string)joParam["name"];
                string number = (string)joParam["number"];
                string imgUrl = (string)joParam["imgUrl"];
                int districtID = (int)joParam["districtID"];
                int hallID = (int)joParam["hallID"];
                int dishesClassID = (int)joParam["dishesClassID"];
                string remark = "";
                if (joParam.Property("remark") != null)
                {
                    remark = (string)joParam["remark"];
                }
                bool isEnable = (bool)joParam["isEnable"];
                JArray jaDishesAttribute = (JArray)joParam["dishesAttribute"];
                JObject joDishesAttribute = new JObject();
                JObject joDishesAttributeResult = new JObject();
                string dbName = bi.GetDBName(districtID);

                int resultLine = 0;
                SqlCommand comm = new SqlCommand();
                if (state == "add")
                {
                    if (!IsDishesNumber(number, districtID, hallID, dishesClassID, dbName))
                    {
                        comm.CommandText = "INSERT INTO Dishes(Number,Name,imgUrl,DistrictID,hallID,dishesClassID,remark,isEnable) VALUES(@Number,@Name,@imgUrl,@DistrictID,@hallID,@dishesClassID,@remark,1)";

                        comm.Parameters.AddWithValue("@Number", number);
                        comm.Parameters.AddWithValue("@Name", name);
                        comm.Parameters.AddWithValue("@imgUrl", imgUrl);
                        comm.Parameters.AddWithValue("@DistrictID", districtID);
                        comm.Parameters.AddWithValue("@hallID", hallID);
                        comm.Parameters.AddWithValue("@dishesClassID", dishesClassID);
                        comm.Parameters.AddWithValue("@remark", remark);

                        resultLine = pd.ExeSqlCode(comm, dbName);

                        if (resultLine > 0)
                        {
                            //循环添加菜品属性
                            id = GetDishesID(number, districtID, hallID, dishesClassID, dbName);
                            for (int dishesAttributeIndex = 0; dishesAttributeIndex < jaDishesAttribute.Count; dishesAttributeIndex++)
                            {
                                joDishesAttribute = (JObject)jaDishesAttribute[dishesAttributeIndex];
                                joDishesAttribute["dishesID"] = id;
                                joDishesAttribute["hallID"] = hallID;
                                joDishesAttributeResult = OptDishesAttribute(joDishesAttribute);

                                if (!(bool)joDishesAttributeResult["state"])
                                {
                                    //删除添加的残余信息 且报错
                                    DelDishes(id, dbName);
                                    joResult["message"] = "添加菜品 " + number + " 失败，请检查菜品属性是否重复";
                                    joResult["data"] = resultLine;
                                    joResult["state"] = false;
                                    return joResult;
                                }
                            }

                            joResult["message"] = "添加菜品 " + number + " 成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "添加菜品 " + number + " 失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["data"] = 0;
                        joResult["message"] = "菜品编码已经存在，请重新添加";
                        joResult["state"] = false;
                        return joResult;
                    }
                }
                else if (state == "modify")
                {
                    comm.CommandText = "UPDATE Dishes SET Name = @Name,ImgUrl = @imgUrl,DistrictID = @DistrictID,hallID=@hallID,dishesClassID=@dishesClassID,remark=@remark,isEnable = @isEnable WHERE id = @id ";

                    comm.Parameters.AddWithValue("@id", id);
                    comm.Parameters.AddWithValue("@Name", name);
                    comm.Parameters.AddWithValue("@imgUrl", imgUrl);
                    comm.Parameters.AddWithValue("@DistrictID", districtID);
                    comm.Parameters.AddWithValue("@hallID", hallID);
                    comm.Parameters.AddWithValue("@dishesClassID", dishesClassID);
                    comm.Parameters.AddWithValue("@isEnable", isEnable);
                    comm.Parameters.AddWithValue("@remark", remark);

                    resultLine = pd.ExeSqlCode(comm, dbName);

                    if (resultLine > 0)
                    {
                        var jaNewDishesAttribute = new JArray(jaDishesAttribute.OrderBy(x => x["attributeID"]));
                        int attributeID = 0;
                        //修改属性时执行 
                        for (int dishesAttributeIndex = 0; dishesAttributeIndex < jaNewDishesAttribute.Count; dishesAttributeIndex++)
                        {
                            joDishesAttribute = (JObject)jaNewDishesAttribute[dishesAttributeIndex];
                            if (attributeID == (int)joDishesAttribute["attributeID"] && (string)joDishesAttribute["state"] == "modify")
                            {
                                //修改时发现重复属性且该项处于修改状态时，则执行删除 
                                joDishesAttribute["state"] = "del";
                            }
                            else
                            {
                                attributeID = (int)joDishesAttribute["attributeID"];
                            }
                            joDishesAttribute["dishesID"] = id;
                            joDishesAttribute["hallID"] = hallID;
                            joDishesAttributeResult = OptDishesAttribute(joDishesAttribute);
                            if (!(bool)joDishesAttributeResult["state"])
                            {
                                //修改成功则直接应用，修改某一项失败 则不做任何处理，提示用户修改失败，但有部分修改可能成功 
                                joResult["message"] = "修改菜品 " + number + " 失败，请检查菜品属性是否重复";
                                joResult["data"] = resultLine;
                                joResult["state"] = false;
                                return joResult;
                            }
                        }

                        joResult["message"] = "修改菜品 " + number + " 成功";
                        joResult["data"] = resultLine;
                        joResult["state"] = true;
                    }
                    else
                    {
                        joResult["message"] = "修改菜品 " + number + " 失败";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                }
                else if (state == "del")
                {
                    if (!IsDishesCiteUserDishes(id, dbName))
                    {
                        bool isDelete = DelDishes(id, dbName);
                        if (isDelete)
                        {
                            joResult["message"] = "删除菜品 " + number + " 成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "删除菜品 " + number + " 失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["message"] = "菜品 " + number + " 已被使用，无法删除";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult["data"] = 0;
                joResult["message"] = ex.Message + "\n" + ex.StackTrace;
                joResult["state"] = false;
            }

            return joResult;
        }

        /// <summary>
        /// 判断属性编码是否存在
        /// </summary>
        /// <returns></returns>
        private bool IsAttributeNumber(string number, int districtID, int hallID, string dbName)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select id from Attribute where number = @number and districtID = @districtID and hallID = @hallID "
                };
                comm.Parameters.AddWithValue("@number", number);
                comm.Parameters.AddWithValue("@districtID", districtID);
                comm.Parameters.AddWithValue("@hallID", hallID);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 判断属性编码是否被菜品分类编码使用
        /// </summary>
        /// <returns></returns>
        private bool IsAttributeCiteDishesAttribute(int id, string dbName)
        {
            try
            {
                SqlCommand comm = new SqlCommand()
                {
                    CommandText = "select ID from DishesAttribute where DishesID = @id"
                };
                comm.Parameters.AddWithValue("@id", id);
                JArray ja = qd.GetData(comm, dbName);

                if (ja.Count > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
                logger.Error(ex);
                return false;
            }
        }

        /// <summary>
        /// 新增、修改、删除属性
        /// </summary>
        /// <returns></returns>
        public JObject OptAttribute(JObject joParam)
        {
            JObject joResult = new JObject();

            try
            {
                int id = 0;
                //新增时 ID 可以不传
                if (joParam.Property("id") != null)
                {
                    id = (int)joParam["id"];
                }
                string state = (string)joParam["state"];
                string name = (string)joParam["name"];
                string number = (string)joParam["number"];
                int districtID = (int)joParam["districtID"];
                int hallID = (int)joParam["hallID"];
                int type = (int)joParam["type"];
                string remark = "";
                if (joParam.Property("remark") != null)
                {
                    remark = (string)joParam["remark"];
                }
                string dbName = bi.GetDBName(districtID);

                int resultLine = 0;
                SqlCommand comm = new SqlCommand();
                if (state == "add")
                {
                    if (!IsAttributeNumber(number, districtID, hallID, dbName))
                    {
                        comm.CommandText = "INSERT INTO Attribute(Number,Name,type,DistrictID,hallID,remark) VALUES(@Number,@Name,@type,@DistrictID,@hallID,@remark)";

                        comm.Parameters.AddWithValue("@Number", number);
                        comm.Parameters.AddWithValue("@Name", name);
                        comm.Parameters.AddWithValue("@type", type);
                        comm.Parameters.AddWithValue("@DistrictID", districtID);
                        comm.Parameters.AddWithValue("@hallID", hallID);
                        comm.Parameters.AddWithValue("@remark", remark);

                        resultLine = pd.ExeSqlCode(comm, dbName);

                        if (resultLine > 0)
                        {
                            joResult["message"] = "添加属性 " + number + " 成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "添加属性 " + number + " 失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["data"] = 0;
                        joResult["message"] = "属性编码已经存在，请重新添加";
                        joResult["state"] = false;
                        return joResult;
                    }
                }
                else if (state == "modify")
                {
                    comm.CommandText = "UPDATE Attribute SET Name = @Name,type = @type,DistrictID = @DistrictID,hallID=@hallID,remark=@remark WHERE id = @id ";

                    comm.Parameters.AddWithValue("@id", id);
                    comm.Parameters.AddWithValue("@Name", name);
                    comm.Parameters.AddWithValue("@type", type);
                    comm.Parameters.AddWithValue("@DistrictID", districtID);
                    comm.Parameters.AddWithValue("@hallID", hallID);
                    comm.Parameters.AddWithValue("@remark", remark);

                    resultLine = pd.ExeSqlCode(comm, dbName);

                    if (resultLine > 0)
                    {
                        joResult["message"] = "修改属性 " + number + " 成功";
                        joResult["data"] = resultLine;
                        joResult["state"] = true;
                    }
                    else
                    {
                        joResult["message"] = "修改属性 " + number + " 失败";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                }
                else if (state == "del")
                {
                    if (!IsAttributeCiteDishesAttribute(id, dbName))
                    {
                        comm.CommandText = "DELETE FROM Attribute WHERE id = @id ";

                        comm.Parameters.AddWithValue("@id", id);

                        resultLine = pd.ExeSqlCode(comm, dbName);

                        if (resultLine > 0)
                        {
                            joResult["message"] = "删除属性 " + number + " 成功";
                            joResult["data"] = resultLine;
                            joResult["state"] = true;
                        }
                        else
                        {
                            joResult["message"] = "删除属性 " + number + " 失败";
                            joResult["data"] = resultLine;
                            joResult["state"] = false;
                        }
                    }
                    else
                    {
                        joResult["message"] = "属性“" + name + "”已被使用，无法删除";
                        joResult["data"] = resultLine;
                        joResult["state"] = false;
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
                Console.Write(ex);
                joResult["data"] = 0;
                joResult["message"] = ex.Message + "\n" + ex.StackTrace;
                joResult["state"] = false;
            }

            return joResult;
        }
    }
}
